-- System Queries:
------------------
/*	SELECT * FROM SGLDBMP01.INFORMATION_SCHEMA.TABLES
	SELECT * FROM sys.tables
*/

USE [SGLDBMP01]
GO

/*					############### 01. Table: [User] ###############					*/

DECLARE @TABLE_NAME		VARCHAR(50);
SET		@TABLE_NAME		= 'User';

DECLARE @TABLE_SCHEMA	VARCHAR(5);
SET		@TABLE_SCHEMA	= 'dbo';

IF (
	EXISTS (
		SELECT	[TABLE_NAME] 
		FROM	SGLDBMP01.INFORMATION_SCHEMA.TABLES
		WHERE	TABLE_NAME		= @TABLE_NAME
			AND	TABLE_SCHEMA	= @TABLE_SCHEMA
			)
	)
	-- Condition satisfied table with this name already exists in this Database
	PRINT 'Table : ' + @TABLE_NAME + ', with the name already exists in the database.'
ELSE
	BEGIN

		CREATE TABLE dbo.[User] (
			  UserId		BIGINT		NOT NULL IDENTITY(1, 1)
			, Name			VARCHAR(100)NOT NULL
			, EMail			NVARCHAR(90)NOT NULL
			, DateCreated	DATETIME	NOT NULL
			, CreatedBy		VARCHAR(100)NOT NULL
			, IsActive		CHAR(1)		NOT NULL );
	
		-- Constraint Naming Convention: [ConstraintType_TableName_ColumnName]
		-- Set EId as the Primary Key for the table dbo.User
		ALTER TABLE dbo.[User] 
			ADD CONSTRAINT PKey_User_UserId PRIMARY KEY (UserId);			

		-- By default if no data is provided to CreatedBy then it will be updated as SYSTEM
		ALTER TABLE dbo.[User] 
			ADD CONSTRAINT DefaultConstraint_User_CreatedBy
			DEFAULT 'SYSTEM' FOR CreatedBy;

		-- By default the current DateTime will be added to the row
		ALTER TABLE dbo.[User] 
			ADD CONSTRAINT DefaultConstraint_User_DateCreated
			DEFAULT GETDATE() FOR DateCreated;

		-- Allowed values for IsActvive is 1 (User active) and 0 (User inActive)
		-- The Default value for this column in always 1 (User is active)
		-- Multiple constraints in single ALTER statement, WITH CHECK.. to ensure the constraints are valid
		ALTER TABLE dbo.[User] WITH CHECK ADD
			CONSTRAINT CheckConstraint_User_IsActive CHECK (IsActive = 'Y' OR IsActive = 'N'),
			CONSTRAINT DefaultConstraint_User_IsActive DEFAULT 'Y' FOR IsActive;

		IF (
			EXISTS (
				SELECT	1 
				FROM	sys.tables
				WHERE	name = @TABLE_NAME
					AND type = 'U'
					)
			)
			PRINT 'Table : ' + @TABLE_NAME + ', created successfully'
		ELSE
			PRINT 'Table : ' + @TABLE_NAME + ', creation failed, kindly check the issue'
	END

/*	Output (01): Table : User, created successfully
	Output (02): Table : User, with the name already exists in the database.
	Output (03): 
	Msg 2705, Level 16, State 4, Line 49
	Column names in each table must be unique. Column name 'UserId' in table 'dbo.User' is specified more than once.
*/